I'm having trouble subtracting groups from other groups.
I've got a data model that has the following essential features:
create table contacts (num int, properties....);
create table groups (groupNum int, contactNum int);
Where not all contacts will be in a group, some groups will contain most contacts, and there will be something like
hundredsof groups and tens of thousands of contacts. I allow people to build groups using criteria, which I need to
programaticallytranslate to sql.
One somewhat common pattern is:
Select all contacts in group a, who have property b, and who aren't in groups c,d,e,f...
My first shot was subqueries:
select num, p1,p2 ... from contacts inner join groups using (contacts.num=groups.contactNum) where
groups.groupNum=a and contact.p3=b and not num in (select contactNum from groups where groupNum=c) and not num in
(selectcontactNum from groups where groupNum=d) and not num in (select contactNum from groups where groupNum=e) and
notnum in (select contactNum from groups where groupNum=f)
This is .... slow. agonizingly so.
With an inner join, I'm not convinced that the subtraction is actually correct., but it is much faster. Unfortunatley,
fasterincorrect answers are rarely helpful.
Outer joins seem even worse than subselects for speed, but it does appear to give the correct answer. (example with a
singlejoin.)
select num from contacts left outer join groups on (contacts.num=groups.contactNum and groups.groupNum=b)
where dl_groupDonor._groupNum is null and p3=c
I've got to be missing something here, because this is much slower from the (slow) procedural system that I'm porting
from.
I've been avoiding using union / intersect since I don't really ever know what columns are going to be in the query.
perhapsI should revisit that decision and try to work around it.
eric